package library.logic;


import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Vector;
import java.util.logging.Logger;

/**
 * User: RockCrazy
 * Date: 09.12.11
 * Time: 16:50
 */
public class ManagementSystem
{
	static Logger log = Logger.getLogger(ManagementSystem.class.getName());
	private static Connection con;
	private static ManagementSystem instance;

	/**
	 *
	 * @throws Exception
	 */
	public ManagementSystem() throws Exception
	{
		try
		{
			Config.load();
			Class.forName(Config.DATABASE_DRIVER);
        	con = DriverManager.getConnection(Config.DATABASE_URL, Config.DATABASE_LOGIN, Config.DATABASE_PASSWORD);
        }
		catch (ClassNotFoundException e)
		{
            throw new Exception(e);
        }
		catch (SQLException e)
		{
            throw new Exception(e);
        }

	}

	public static synchronized ManagementSystem getInstance() throws Exception
	{
        if (instance == null)
		{
            instance = new ManagementSystem();
        }
        return instance;
	}

	/**
	 * Строки в запросе
	 * @param query
	 * @return
	 * @throws SQLException
	 */
	public ResultSet stringSQL(String query) throws SQLException
	{
		ResultSet rs = null;
        PreparedStatement stmt = null;
        try
		{
			Statement st = con.createStatement();
			rs = st.executeQuery(query);
        }
		finally
		{
			
        }
		return rs;
    }


	/**
	 * Вставка данных читателя в БД
	 * @param reader
	 * @throws SQLException
	 */
	public void insertReader(Reader reader) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("INSERT INTO reader " +
					"(fam, name, otch, birthday, date_reg, passport, adress, phone) " +
					"VALUES (?,?,?,?,?,?,?,?)");
			stmt.setString(1, reader.getReaderFam());
			stmt.setString(2, reader.getReaderName());
			stmt.setString(3, reader.getReaderOtch());
			stmt.setDate(4, new Date(reader.getReaderBirthday().getTime()));
			stmt.setDate(5, new Date(reader.getReaderDateReg().getTime()));
			stmt.setString(6, reader.getReaderPassport());
			stmt.setString(7, reader.getReaderAdress());
			stmt.setString(8, reader.getReaderPhone());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	/**
	 * Обновляем данные о читателе
	 * @param reader
	 * @throws SQLException
	 */
	public void updateReader(Reader reader) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("UPDATE reader SET " +
					"fam=?, name=?, otch=?, birthday=?, date_reg=?, passport=?,  adress=?, phone=?" +
					"WHERE id_reader=?");
			stmt.setString(1, reader.getReaderFam());
			stmt.setString(2, reader.getReaderName());
			stmt.setString(3, reader.getReaderOtch());
			stmt.setDate(4, new Date(reader.getReaderBirthday().getTime()));
			stmt.setDate(5, new Date(reader.getReaderDateReg().getTime()));
			stmt.setString(6, reader.getReaderPassport());
			stmt.setString(7, reader.getReaderAdress());
			stmt.setString(8, reader.getReaderPhone());
			stmt.setInt(9, reader.getReaderId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}


	/**
	 * Удаление читателя из БД
	 * @param reader
	 * @throws SQLException
	 */
	public void deleteReader(Reader reader) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("DELETE FROM reader WHERE id_reader=?");
			stmt.setInt(1, reader.getReaderId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	/**
	 * Получаем список читателей
	 * @return
	 * @throws SQLException
	 */
	public List<Reader> getReaders() throws SQLException
	{
		List<Reader> readers = new ArrayList<Reader>();

		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT id_reader, fam, name, otch, birthday, date_reg, passport, adress, phone  " + "FROM reader");
			while (rs.next())
			{
				Reader rdr = new Reader();
				rdr.setReaderId(rs.getInt(1));
				rdr.setReaderFam(rs.getString(2));
				rdr.setReaderName(rs.getString(3));
				rdr.setReaderOtch(rs.getString(4));
				rdr.setReaderBirthday(rs.getDate(5));
				rdr.setReaderDateReg(rs.getDate(6));
				rdr.setReaderPassport(rs.getString(7));
				rdr.setReaderAdress(rs.getString(8));
				rdr.setReaderPhone(rs.getString(9));
				readers.add(rdr);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			if (stmt != null)
			{
				stmt.close();
			}
		}
		return readers;
	}

	/**
	 *  Вносим новую книгу
	 * @param book
	 * @throws SQLException
	 */
	public void insertBook(Book book) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("INSERT INTO book " +
					"(book_name, author, izdatel, num_izdan, year_izdan, kolvo_str, price, id_tema, exist, date_prihod," +
					"letter_code, polka, prim, oglav, tag) " +
					"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			stmt.setString(1, book.getBookName());
			stmt.setString(2, book.getAuthor());
			stmt.setString(3, book.getIzdatel());
			stmt.setString(4, String.valueOf(book.getNumIzdan()));
			stmt.setString(5, String.valueOf(book.getYearIzdan()));
			stmt.setString(6, String.valueOf(book.getKolvoStr()));
			stmt.setDouble(7, book.getPrice());
			stmt.setString(8, String.valueOf(book.getTemaId()));
			stmt.setBoolean(9, book.isExist());
			stmt.setDate(10, new Date(book.getDatePrihod().getTime()));
			stmt.setString(11, book.getLetterCode());
			stmt.setString(12, book.getPolka());
			stmt.setString(13, book.getPrim());
			stmt.setString(14, book.getOglav());
			stmt.setString(15, book.getTag());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	/**
	 * Обновляем запись в таблице КНИГА
	 * @param book
	 * @throws SQLException
	 */
	public void updateBook(Book book) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("UPDATE book SET " +
					"book_name=?, author=?, izdatel=?, num_izdan=?, year_izdan=?, kolvo_str=?, price=?, " +
					"id_tema=?, exist=?, date_prihod=?, " +
					"letter_code=?, polka=?, prim=?, oglav=?, tag=? " +
					"WHERE id_book=?");
			stmt.setString(1, book.getBookName());
			stmt.setString(2, book.getAuthor());
			stmt.setString(3, book.getIzdatel());
			stmt.setString(4, String.valueOf(book.getNumIzdan()));
			stmt.setString(5, String.valueOf(book.getYearIzdan()));
			stmt.setString(6, String.valueOf(book.getKolvoStr()));
			stmt.setDouble(7, book.getPrice());
			stmt.setString(8, String.valueOf(book.getTemaId()));
			stmt.setBoolean(9, book.isExist());
			stmt.setDate(10, new Date(book.getDatePrihod().getTime()));
			stmt.setString(11, book.getLetterCode());
			stmt.setString(12, book.getPolka());
			stmt.setString(13, book.getPrim());
			stmt.setString(14, book.getOglav());
			stmt.setString(15, book.getTag());
			stmt.setInt(16, book.getBookId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	/**
	 * Удаляем книгу из БД
	 * @param book
	 * @throws SQLException
	 */
	public void deleteBook(Book book) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("DELETE FROM book WHERE id_book=?");
			stmt.setInt(1, book.getBookId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	/**
	 * Создаем список книг
	 * @return
	 * @throws SQLException
	 */
	public List<Book> getBooks() throws SQLException
	{
		List<Book> books = new ArrayList<Book>();

		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT id_book, book_name, author, izdatel, num_izdan, year_izdan, kolvo_str, " +
					"price, id_tema, exist, date_prihod, " +
					"letter_code, polka, prim, oglav, tag  " +
					"FROM book");
			while (rs.next())
			{
				Book book = new Book();
				book.setBookId(rs.getInt(1));
				book.setBookName(rs.getString(2));
				book.setAuthor(rs.getString(3));
				book.setIzdatel(rs.getString(4));
				book.setNumIzdan(rs.getInt(5));
				book.setYearIzdan(rs.getInt(6));
				book.setKolvoStr(rs.getInt(7));
				book.setPrice(rs.getDouble(8));
				book.setTemaId(rs.getInt(9));
				book.setExist(Boolean.valueOf(rs.getString(10)));
				book.setDatePrihod(rs.getDate(11));
				book.setLetterCode(rs.getString(12));
				book.setPolka(rs.getString(13));
				book.setPrim(rs.getString(14));
				book.setOglav(rs.getString(15));
				book.setTag(rs.getString(16));
				books.add(book);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			if (stmt != null)
			{
				stmt.close();
			}
		}
		return books;
	}

	/**
	 * Добавляем в БД новую тему
	 * @param tema
	 * @throws SQLException
	 */
	public void insertTema(Tema tema) throws SQLException
	{

		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement(
					"INSERT INTO tema " +
					"(tema_name) " +
					"VALUES (?)");
			stmt.setString(1, tema.getTemaName());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	/**
	 * Обновляем данные в таблице по теме
	 * @param tema
	 * @throws SQLException
	 */
	public void updateTema(Tema tema) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement(
					"UPDATE tema SET " +
					"tema_name=? " +
					"WHERE id_tema=?");
			stmt.setString(1, tema.getTemaName());
			stmt.setInt(2, tema.getTemaId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	/**
	 * уДАЛЯЕМ ТЕМУ ИЗ бд
	 * @param tema
	 * @throws SQLException
	 */
	public void deleteTema(Tema tema) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("DELETE FROM tema WHERE id_tema=?");
			stmt.setInt(1, tema.getTemaId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	public List<Tema> getTems() throws SQLException
	{
		List<Tema> tems = new ArrayList<Tema>();

		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT id_tema, tema_name " +
					"FROM tema");
			while (rs.next())
			{
				Tema tema = new Tema();
				tema.setTemaId(rs.getInt(1));
				tema.setTemaName(rs.getString(2));
				tems.add(tema);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			if (stmt != null)
			{
				stmt.close();
			}
		}
		return tems;
	}

	public void insertPost(Post post) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("INSERT INTO post " +
					"(post_name, post_adress, post_tel, post_url) " +
					"VALUES (?,?,?,?)");
			stmt.setString(1, post.getPostName());
			stmt.setString(2, post.getPostAdress());
			stmt.setString(3, post.getPostPhone());
			stmt.setString(4, post.getPostURL());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	public void updatePost(Post post) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("UPDATE post SET " +
					"post_name=?, post_adress=?, post_tel=?, post_url=? " +
					"WHERE id_post=?");
			stmt.setString(1, post.getPostName());
			stmt.setString(2, post.getPostAdress());
			stmt.setString(3, post.getPostPhone());
			stmt.setString(4, post.getPostURL());
			stmt.setInt(5, post.getPostId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	public void deletePost(Post post) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("DELETE FROM post WHERE id_post=?");
			stmt.setInt(1, post.getPostId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	public List<Post> getPosts() throws SQLException
	{
		List<Post> posts = new ArrayList<Post>();

		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT id_post, post_name, post_adress, post_tel, post_url " + "FROM post");
			while (rs.next())
			{
				Post post = new Post();
				post.setPostId(rs.getInt(1));
				post.setPostName(rs.getString(2));
				post.setPostAdress(rs.getString(3));
				post.setPostPhone(rs.getString(4));
				post.setPostURL(rs.getString(5));
				posts.add(post);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			if (stmt != null)
			{
				stmt.close();
			}
		}
		return posts;
	}

	public void closeRS(ResultSet rs) throws SQLException
	{
		if (rs != null)
		{
			rs.close();
		}
	}

	/**
	 * Вставляем новую запись в журнал
	 * @param journal
	 * @throws SQLException
	 */
	public void insertJournal(Journal journal) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("INSERT INTO journal " +
					"(id_book, id_reader, date_vidano, date_vozvrat, sdano) " +
					"VALUES (?,?,?,?,?)");
			stmt.setInt(1, journal.getBookId());
			stmt.setInt(2, journal.getReaderId());
			stmt.setDate(3, new Date(journal.getDateVidano().getTime()));
			stmt.setDate(4, new Date(journal.getDateVozvrat().getTime()));
			stmt.setBoolean(5, journal.isSdano());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	public void updateJournal(Journal journal) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("UPDATE journal SET " +
					"id_book=?, id_reader=?, date_vidano=?, date_vozvrat=?, sdano=? " +
					"WHERE journal_id=?");
			stmt.setInt(1, journal.getBookId());
			stmt.setInt(2, journal.getReaderId());
			stmt.setDate(3, new Date(journal.getDateVidano().getTime()));
			stmt.setDate(4, new Date(journal.getDateVozvrat().getTime()));
			stmt.setBoolean(5, journal.isSdano());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	public void deleteJournal(Journal journal) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = con.prepareStatement("DELETE FROM journal WHERE id_journal=?");
			stmt.setInt(1, journal.getJournalId());
			stmt.execute();
		}
		finally
		{
			if (stmt != null)
			{
				stmt.close();
			}
		}
	}

	public List<Journal> getJournals() throws SQLException
	{
		List<Journal> journals = new ArrayList<Journal>();

		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT id_journal, id_book, id_reader, date_vidano, date_vozvrat, sdano " +
					"FROM journal");
			while (rs.next())
			{
				Journal j = new Journal();
				j.setJournalId(rs.getInt(1));
				j.setBookId(rs.getInt(2));
				j.setReaderId(rs.getInt(3));
				j.setDateVidano(rs.getDate(4));
				j.setDateVozvrat(rs.getDate(5));
				j.setSdano(rs.getBoolean(6));
				journals.add(j);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			if (stmt != null)
			{
				stmt.close();
			}
		}
		return journals;
	}
}
